from flask import Flask,render_template,request
import json
import pymysql
from pymysql.cursors import DictCursor


app = Flask(__name__)


'''
数据查询--封装

'''
@app.route("/data0", methods=["GET"])
def getData():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    return_dict['result'] = 'true'
    return json.dumps(return_dict, ensure_ascii=False)

#  区汇总  -- area
@app.route("/data1", methods=["GET"])
def getData1():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT * FROM ads_region_count;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

# data2 价格汇总  700以下 / 700~1200  / 1200~2000 / 2000以上   -- price
@app.route("/data2", methods=["GET"])
def getData2():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT * FROM ads_price_count;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

# data3 租房方式  合租/证租  way
@app.route("/data3", methods=["GET"])
def getData3():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT * FROM ads_way_count;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

# data4 房屋朝向汇总 orientation
@app.route("/data4", methods=["GET"])
def getData4():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT * FROM ads_orie_count;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

# data5 维护时间 maintain
@app.route("/data5", methods=["GET"])
def getData5():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT REPLACE(t.maintain, '今天', '0') maintain, `count` FROM (SELECT REPLACE(maintain, '天前', '') maintain, `count` FROM ads_maintain_count)t ORDER BY CONVERT(t.maintain,SIGNED) ASC;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

# data6 价格/平方
@app.route("/data6", methods=["GET"])
def getData6():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT * FROM (SELECT price, CONVERT(AVG(m),SIGNED) m FROM ads_price_m GROUP BY price) t ORDER BY CONVERT(price,SIGNED) ASC;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

# data7 价格趋势图
@app.route("/data7", methods=["GET"])
def getData7():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT * FROM (SELECT price, COUNT(8) `count`FROM ads_price_m GROUP BY price)t ORDER BY CONVERT(price,SIGNED) ASC;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

# data8 价格趋势图
@app.route("/data8", methods=["GET"])
def getData8():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT * FROM (SELECT m, COUNT(8) `count`FROM ads_price_m GROUP BY m)t ORDER BY CONVERT(m,SIGNED) ASC;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

# data9 面积
@app.route("/data9", methods=["GET"])
def getData9():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT * FROM ads_type_count;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

# data10 价格趋势图
@app.route("/data10", methods=["GET"])
def getData10():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT * FROM ads_staying_count;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

# data11 楼层   floor
@app.route("/data11", methods=["GET"])
def getData11():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT * FROM (SELECT * FROM ads_floor_count ORDER BY CONVERT(`count`,SIGNED) DESC)t LIMIT 10 , 20;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

# 租期  tenancy_term
@app.route("/data12", methods=["GET"])
def getData12():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT * FROM ads_term_count;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

# 车位信息  stall
@app.route("/data13", methods=["GET"])
def getData13():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT * FROM ads_stall_count;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

# 用水  water
@app.route("/data14", methods=["GET"])
def getData14():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT * FROM ads_water_count;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

# 用电  electror
@app.route("/data15", methods=["GET"])
def getData15():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT * FROM ads_electro_count;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

# 燃气  fuel_gas
@app.route("/data16", methods=["GET"])
def getData16():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT * FROM ads_fuelgas_count;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

# 采暖方式 heating
@app.route("/data17", methods=["GET"])
def getData17():
    # 默认返回内容
    return_dict = {'code': 200, 'result': False, 'msg': '请求成功'}
    # 对参数进行操作
    sql = "SELECT * FROM ads_heating_count;"
    return_dict['result'] = getData(sql)
    return json.dumps(return_dict, ensure_ascii=False)

'''
功能函数-查询模板
** 存在sql注入
'''
def getData(sql):
    url = "192.168.6.166"
    db = "zjp"
    userName = "root"
    pws = "123456"
    conn = pymysql.connect(host = url, database = db, user = userName, password = pws)
    cursor = conn.cursor(DictCursor)
    cursor.execute(sql)
    result = cursor.fetchall()
    conn.close()
    return result


# 主页路径
@app.route('/')
def hello_world():  # put application's code here
    return render_template("index.html")


# 启动
if __name__ == '__main__':
    # app.run(host='127.0.0.1', port=5000)
    app.run()